# This file contains the class UserInfoTableMethod which is used to operate the user_info table in the database.

'''
-- 用户信息基础表
CREATE TABLE user_info (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    phone VARCHAR(11) NOT NULL,
    account_status VARCHAR(20) DEFAULT 'normal',
    password VARCHAR(32) NOT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
        
'''



class UserInfoTableMethod:
    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    def select_all_user_info(self, limit=30, page=1):
        '''user_info table select all user info '''

        query = f"SELECT * FROM user_info LIMIT %s OFFSET %s"

        self.cursor.execute(query, (limit, (page-1)*limit))

        result = self.cursor.fetchall()

        return result
    
    def select_user_info_by_id(self, user_id):
        '''user_info table select user info by id'''

        query = f"SELECT * FROM user_info WHERE user_id = %s"

        self.cursor.execute(query, (user_id,))

        result = self.cursor.fetchone()

        return result

    def insert_user_info(self, username, phone, password):
        '''user_info table insert user info'''


        query = f"INSERT INTO user_info (username, phone, password) VALUES (%s, %s, %s)"

        self.cursor.execute(query, (username, phone, password))

        self.conn.commit()

        return self.cursor.lastrowid

    def update_user_info(self, user_id, username=None, phone=None, password=None, account_status=None):
        '''user_info table update user info'''


        query = f"UPDATE user_info SET "

        if username:
            query += f"username = %s, "

        if phone:
            query += f"phone = %s, "

        if password:
            query += f"password = %s, "

        if account_status:
            query += f"account_status = %s, "

        query = query[:-2]

        query += f" WHERE user_id = %s"

        values = []

        if username:
            values.append(username)

        if phone:
            values.append(phone)

        if password:
            values.append(password)

        if account_status:
            values.append(account_status)

        values.append(user_id)

        self.cursor.execute(query, values)

        self.conn.commit()

        return True

    def delete_user_info(self, user_id):
        '''user_info table delete user info'''

        query = f"DELETE FROM user_info WHERE user_id = %s"

        self.cursor.execute(query, (user_id,))

        self.conn.commit()

        return True
    
    def select_user_info_by_username_and_password(self, username, password):
        '''user_info table select user info by username and password'''


        query = f"SELECT * FROM user_info WHERE username = %s AND password = %s"


        self.cursor.execute(query, (username, password))


        result = self.cursor.fetchone()

        return result
    
    def select_user_info_by_username(self, username):
        '''user_info table select user info by username'''

        query = f"SELECT * FROM user_info WHERE username = %s"

        self.cursor.execute(query, (username,))

        result = self.cursor.fetchone()

        return result
    
    def select_user_info_by_phone(self, phone):
        '''user_info table select user info by phone'''

        query = f"SELECT * FROM user_info WHERE phone = %s"

        self.cursor.execute(query, (phone,))

        result = self.cursor.fetchone()

        return result
    
    def select_user_info_by_username_or_phone(self, username, phone):
        '''user_info table select user info by username or phone'''

        query = f"SELECT * FROM user_info WHERE username = %s OR phone = %s"

        self.cursor.execute(query, (username, phone))


        result = self.cursor.fetchone()


        return result

